﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Runtime.ExceptionServices;
using System.Text;
using System.Threading.Tasks;

using SchoolManagementSystem.Exceptions;

namespace SchoolManagementSystem.Classes
{
    public class SQLiteConnecter
    {
        SQLiteConnection connection;
        Logger logger;

        private string createCommand1 = "create table schools (" +
            "school_id varchar(16), " +
            "school_name varchar(64), " +
            "address varchar(256), " +
            "build_time varchar(24), " +
            "headmaster varchar(64), " +
            "school_phone varchar(16), " +
            "PRIMARY KEY(school_id))";
        private string createCommand2 = "create table classes (" +
            "school_name varchar(64), " +
            "majority varchar(64), " +
            "grade varchar(16), " +
            "class varchar(16), " +
            "tutor varchar(32), " +
            "PRIMARY KEY(school_name, majority, grade, class))";
        private string createCommand3 = "create table students (" +
            "school_name varchar(64), " +
            "stu_id varchar(16), " +
            "stu_name varchar(32), " +
            "gender varchar(8), " +
            "stu_phone varchar(16), " +
            "majority varchar(128), " +
            "grade varchar(16), " +
            "class varchar(64), " +
            "PRIMARY KEY(school_name, stu_id))";

        public SQLiteConnecter(Logger logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 创建数据库的方法，若存在对应的.sqlite数据库，则抛出异常，否则创建一个新的数据库
        /// </summary>
        /// <param name="sqliteName">数据库名称</param>
        /// <exception cref="ConnectFailedException"></exception>
        public void createSqlite(string sqliteName)
        {
            try
            {
                string type=Path.GetExtension(sqliteName);
                if (!type.Equals(".sqlite"))
                    throw new ConnectFailedException("该文件不是sqlite数据库文件：" + sqliteName);
                if(File.Exists(sqliteName))
                    throw new ConnectFailedException("数据库文件已存在：" + sqliteName);
                SQLiteConnection.CreateFile(sqliteName);
                Console.WriteLine("数据库创建成功");
            }
            catch(ConnectFailedException exp)
            {
                throw exp;
            }
            catch(Exception)
            {
                throw new ConnectFailedException("数据库创建失败：" + sqliteName);
            }
        }

        /// <summary>
        /// 连接Sqlite数据库的方法，同时通过“create table”语句创建数据表，
        /// 若数据库连接失败，则抛出异常，
        /// 若数据表已存在，则抛出异常
        /// </summary>
        /// <param name="sqliteName">Sqlite数据库的名称</param>
        /// <exception cref="ConnectFailedException"></exception>
        public void connectSqlite(string sqliteName)
        {
            try
            {
                string openCommand = "Data Source=" + sqliteName + "; version=3;";
                connection = new SQLiteConnection(openCommand);
                connection.Open();
                Console.WriteLine("数据库连接成功");
            }
            catch(Exception)
            {
                throw new ConnectFailedException("连接该数据库失败："+sqliteName);
            }
            
            try
            {
                new SQLiteCommand(createCommand1, connection).ExecuteNonQuery();
                new SQLiteCommand(createCommand2, connection).ExecuteNonQuery();
                new SQLiteCommand(createCommand3, connection).ExecuteNonQuery();
                Console.WriteLine("数据库初始化成功");
                logger.AddToLog(DateTime.Now.ToString() + ": " + createCommand1 + "\n\n" +
                    DateTime.Now.ToString() + ": " + createCommand2 + "\n\n" +
                    DateTime.Now.ToString() + ": " + createCommand3 + "\n");
            }
            catch (Exception)
            {
                throw new ConnectFailedException("数据库已被初始化");
            }
        }

        /// <summary>
        /// 被外部调用的插入方法，可根据需要进行定制
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="KeyValues">属性（column）与value的键值对的集合</param>
        public void insert(string tableName, List<KeyValuePair<string, string>> KeyValues)
        {
            if (checkIfInsertValid(tableName, KeyValues))
                executeCommand_insert_base(tableName, KeyValues);
            else
            {
                if (tableName.Equals("classes"))
                    throw new ConnectFailedException("该班级所属学校不存在");
                else if(tableName.Equals("students"))
                    throw new ConnectFailedException("该学生所属班级不存在");
            }
        }

        /// <summary>
        /// 被外部调用的查询方法，可根据需要进行定制
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="columns">数据表的Column类型</param>
        /// <param name="conditions">查找的额外条件的集合</param>
        /// <returns>返回查询结果，List容器首部元素为Column类型，其它为各属性的Value</returns>
        public List<string[]> select(string tableName, List<string> columns, List< KeyValuePair<string, string>> conditions)
        {
            return executeCommand_select_base(tableName, columns, conditions);
        }

        /// <summary>
        /// 向Sqlite数据库插入信息的基础方法
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="KeyValues">属性（column）与value的键值对的集合</param>
        /// <exception cref="LoggerFailedException"></exception>
        /// <exception cref="ConnectFailedException"></exception>
        private void executeCommand_insert_base(string tableName, List<KeyValuePair<string,string>>KeyValues)
        {
            if (KeyValues.Count < 2)
                return;
            string keys = "", values = "";
            bool isFirst = true;
            foreach(KeyValuePair<string,string> kvp in KeyValues)
            {
                if (isFirst)
                {
                    isFirst = false;
                    keys += kvp.Key;
                    values += "@" + kvp.Key;
                }
                else
                {
                    keys += ", " + kvp.Key;
                    values += ", @" + kvp.Key;
                }
            }
            
            string command = "insert into "+tableName
                +" ("+keys+") values ("+values+")";
            try
            {
                SQLiteCommand sqlCommand = new SQLiteCommand(command, connection);
                foreach(var item in KeyValues)
                {
                    sqlCommand.Parameters.AddWithValue("@"+item.Key, item.Value);
                }
                System.Console.WriteLine(command);
                sqlCommand.ExecuteNonQuery();
                logger.AddToLog(DateTime.Now.ToString() +": "+ command);
            }catch(IOException)
            {
                throw new LoggerFailedException();
            }
            catch (Exception)
            {
                throw new ConnectFailedException("数据插入失败：table = "+tableName);
            }
        }

        /// <summary>
        /// 向Sqlite数据库查询数据功能的基础方法
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="columns">数据表的Column类型</param>
        /// <param name="conditions">查找的额外条件的集合</param>
        /// <returns>返回查询结果，List容器首部元素为Column类型，其它为各属性的Value</returns>
        /// <exception cref="LoggerFailedException"></exception>
        /// <exception cref="ConnectFailedException"></exception>
        private List<string[]> executeCommand_select_base(string tableName, List<string>columns, List<KeyValuePair<string,string>>conditions)
        {
            string column = "";
            int columnCount=columns.Count;
            string[] columnsToShow=new string[columnCount]; int i = 0;
            bool isFirst1=true;
            foreach (string item in columns)
            {
                if (isFirst1) { column += item; isFirst1 = false; }
                else { column += ", " + item; }
                columnsToShow[i++] = item;
            }

            string condition = "";
            if(conditions.Count > 0)
            {
                condition = "where ";
                bool isFirst2 = true;
                foreach (var item in conditions)
                {
                    if (isFirst2) { condition += item.Key+"=@"+item.Key; isFirst2 = false; }
                    else { condition += " and " + item.Key + "=@" + item.Key; }
                }
            }
            string command = "select " + column + " from " + tableName + " " + condition;
            try
            {
                SQLiteCommand sqlCommand = new SQLiteCommand(command, connection);
                foreach(var item in conditions)
                {
                    sqlCommand.Parameters.AddWithValue("@" + item.Key, item.Value);
                }
                System.Console.WriteLine(command);
                sqlCommand.ExecuteNonQuery();
                List<string[]> result = new List<string[]>{columnsToShow};
                SQLiteDataReader reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    string[] line = new string[columnCount]; int j = 0;
                    foreach (string item in columns)
                        line[j++] = reader[item].ToString();
                    result.Add(line);
                }
                logger.AddToLog(DateTime.Now.ToString() + ": " + command);
                return result;
            }
            catch (IOException)
            {
                throw new LoggerFailedException();
            }
            catch (Exception)
            {
                throw new ConnectFailedException("查找数据库失败，table = " + tableName);
            }
        }

        /// <summary>
        /// 判断插入消息是否合法，如：
        /// 插入班级，则学校必然已经存在；
        /// 插入学生，则班级必然已经存在
        /// </summary>
        /// <param name="tableName">插入操作的数据表</param>
        /// <param name="KeyValues">插入的内容（键值对）</param>
        /// <returns></returns>
        private bool checkIfInsertValid(string tableName, List<KeyValuePair<string,string>>KeyValues)
        {
            if (tableName.Equals("schools"))
                return true;
            List<KeyValuePair<string,string>>conditions = new List<KeyValuePair<string, string>>();
            if (tableName.Equals("classes"))
            {
                foreach(var kvp in KeyValues)
                {
                    if (kvp.Key.Equals("school_name"))
                        conditions.Add(new KeyValuePair<string, string>(kvp.Key,kvp.Value));
                }
                List<string> columns = new List<string>() { "school_id" };
                List<string[]> result = executeCommand_select_base("schools", columns, conditions);
                if (result.Count <= 1)
                    return false;
            }
            else if (tableName.Equals("students"))
            {
                foreach (var kvp in KeyValues)
                {
                    if (kvp.Key.Equals("school_name")||
                        kvp.Key.Equals("majority")||
                        kvp.Key.Equals("grade")||
                        kvp.Key.Equals("class"))
                    {
                        conditions.Add(new KeyValuePair<string, string>(kvp.Key,kvp.Value));
                    }
                }
                List<string> columns = new List<string>() { "class" };
                List<string[]> result = executeCommand_select_base("classes", columns, conditions);
                if (result.Count <= 1)
                    return false;
            }
            return true;
        }

        public void close()
        {
            connection.Close();
        }
    }
}
